# -*- coding: utf-8 -*-
from typing import List, Optional, Dict, Any
from sqlalchemy import and_, or_, desc, asc
from sqlalchemy.orm import joinedload

from db.session import with_session
from db.models.material import Material, MaterialTypeDict, FileSourceDict


""" =========================== MaterialTypeDict ================================ """


@with_session
def db_create_material_type(session, name: str) -> Optional[MaterialTypeDict]:
    """创建资料类型"""
    try:
        material_type = MaterialTypeDict(name=name)
        session.add(material_type)
        session.commit()
        session.refresh(material_type)
        return material_type
    except Exception as e:
        session.rollback()
        raise e


@with_session
def db_get_material_type_by_id(session, type_id: int) -> Optional[MaterialTypeDict]:
    """根据ID获取资料类型"""
    return session.query(MaterialTypeDict).filter(MaterialTypeDict.id == type_id).first()


@with_session
def db_get_all_material_types(session) -> List[MaterialTypeDict]:
    """获取所有资料类型"""
    return session.query(MaterialTypeDict).all()


@with_session
def db_update_material_type(session, type_id: int, name: str) -> Optional[MaterialTypeDict]:
    """更新资料类型"""
    try:
        material_type = session.query(MaterialTypeDict).filter(MaterialTypeDict.id == type_id).first()
        if not material_type:
            return None
        material_type.name = name
        session.commit()
        session.refresh(material_type)
        return material_type
    except Exception as e:
        session.rollback()
        raise e


@with_session
def db_delete_material_type(session, type_id: int) -> bool:
    """删除资料类型"""
    try:
        material_type = session.query(MaterialTypeDict).filter(MaterialTypeDict.id == type_id).first()
        if not material_type:
            return False
        session.delete(material_type)
        session.commit()
        return True
    except Exception as e:
        session.rollback()
        raise e


""" =========================== FileSourceDict ================================ """


@with_session
def db_create_file_source(session, name: str) -> Optional[FileSourceDict]:
    """创建文件来源"""
    try:
        file_source = FileSourceDict(name=name)
        session.add(file_source)
        session.commit()
        session.refresh(file_source)
        return file_source
    except Exception as e:
        session.rollback()
        raise e


@with_session
def db_get_file_source_by_id(session, source_id: int) -> Optional[FileSourceDict]:
    """根据ID获取文件来源"""
    return session.query(FileSourceDict).filter(FileSourceDict.id == source_id).first()


@with_session
def db_get_all_file_sources(session) -> List[FileSourceDict]:
    """获取所有文件来源"""
    return session.query(FileSourceDict).all()


@with_session
def db_update_file_source(session, source_id: int, name: str) -> Optional[FileSourceDict]:
    """更新文件来源"""
    try:
        file_source = session.query(FileSourceDict).filter(FileSourceDict.id == source_id).first()
        if not file_source:
            return None
        file_source.name = name
        session.commit()
        session.refresh(file_source)
        return file_source
    except Exception as e:
        session.rollback()
        raise e


@with_session
def db_delete_file_source(session, source_id: int) -> bool:
    """删除文件来源"""
    try:
        file_source = session.query(FileSourceDict).filter(FileSourceDict.id == source_id).first()
        if not file_source:
            return False
        session.delete(file_source)
        session.commit()
        return True
    except Exception as e:
        session.rollback()
        raise e


""" =========================== Material ================================ """


@with_session
def db_create_material(session, material_data: Dict[str, Any]) -> Optional[Material]:
    """创建资料"""
    try:
        material = Material(**material_data)
        session.add(material)
        session.commit()
        session.refresh(material)
        
        # 重新查询以获取关联数据
        material_with_relations = session.query(Material)\
            .options(joinedload(Material.material_type_rel), joinedload(Material.file_source_rel))\
            .filter(Material.id == material.id)\
            .first()
        
        return material_with_relations
    except Exception as e:
        session.rollback()
        raise e


@with_session
def db_get_material_by_id(session, material_id: int) -> Optional[Material]:
    """根据ID获取资料"""
    return session.query(Material)\
        .options(joinedload(Material.material_type_rel), joinedload(Material.file_source_rel))\
        .filter(Material.id == material_id, Material.is_deleted == False)\
        .first()


@with_session
def db_get_material_by_hash(session, file_hash: str) -> Optional[Material]:
    """根据文件哈希获取资料"""
    return session.query(Material)\
        .options(joinedload(Material.material_type_rel), joinedload(Material.file_source_rel))\
        .filter(Material.file_hash == file_hash, Material.is_deleted == False)\
        .first()


@with_session
def db_get_materials(session, page: int = 1, page_size: int = 10, 
                    file_topic: str = None, company: str = None, 
                    material_type: int = None, file_source: int = None) -> Dict[str, Any]:
    """分页获取资料列表"""
    query = session.query(Material)\
        .options(joinedload(Material.material_type_rel), joinedload(Material.file_source_rel))\
        .filter(Material.is_deleted == False)
    
    # 添加过滤条件
    if file_topic:
        query = query.filter(Material.file_topic.like(f"%{file_topic}%"))
    if company:
        query = query.filter(Material.company == company)
    if material_type:
        query = query.filter(Material.material_type == material_type)
    if file_source:
        query = query.filter(Material.file_source == file_source)
    
    # 按创建时间倒序排列
    query = query.order_by(desc(Material.create_time))
    
    # 计算总数
    total = query.count()
    
    # 分页
    materials = query.offset((page - 1) * page_size).limit(page_size).all()
    
    return {
        "materials": materials,
        "total": total,
        "page": page,
        "page_size": page_size,
        "total_pages": (total + page_size - 1) // page_size
    }


@with_session
def db_update_material(session, material_id: int, update_data: Dict[str, Any]) -> Optional[Material]:
    """更新资料"""
    try:
        material = session.query(Material).filter(Material.id == material_id, Material.is_deleted == False).first()
        if not material:
            return None
        
        for key, value in update_data.items():
            if hasattr(material, key):
                setattr(material, key, value)
        
        session.commit()
        session.refresh(material)
        
        # 重新查询以获取关联数据
        material_with_relations = session.query(Material)\
            .options(joinedload(Material.material_type_rel), joinedload(Material.file_source_rel))\
            .filter(Material.id == material_id)\
            .first()
        
        return material_with_relations
    except Exception as e:
        session.rollback()
        raise e


@with_session
def db_delete_material(session, material_id: int) -> bool:
    """逻辑删除资料"""
    try:
        material = session.query(Material).filter(Material.id == material_id, Material.is_deleted == False).first()
        if not material:
            return False
        material.is_deleted = True
        session.commit()
        return True
    except Exception as e:
        session.rollback()
        raise e


@with_session
def db_physical_delete_material(session, material_id: int) -> bool:
    """物理删除资料"""
    try:
        material = session.query(Material).filter(Material.id == material_id).first()
        if not material:
            return False
        session.delete(material)
        session.commit()
        return True
    except Exception as e:
        session.rollback()
        raise e


@with_session
def db_get_materials_by_ids(session, material_ids: List[int]) -> List[Material]:
    """根据ID数组获取资料列表"""
    if not material_ids:
        return []
    
    return session.query(Material)\
        .options(joinedload(Material.material_type_rel), joinedload(Material.file_source_rel))\
        .filter(Material.id.in_(material_ids), Material.is_deleted == False)\
        .order_by(desc(Material.create_time))\
        .all()


@with_session
def db_get_materials_with_fuzzy_search(session, page: int = 1, page_size: int = 10, 
                                     file_topic: str = None, company: str = None, 
                                     material_type: int = None, file_source: int = None,
                                     search_keyword: str = None) -> Dict[str, Any]:
    """分页获取资料列表，支持模糊查询"""
    query = session.query(Material)\
        .options(joinedload(Material.material_type_rel), joinedload(Material.file_source_rel))\
        .filter(Material.is_deleted == False)
    
    # 添加过滤条件
    if file_topic:
        query = query.filter(Material.file_topic.like(f"%{file_topic}%"))
    if company:
        query = query.filter(Material.company == company)
    if material_type:
        query = query.filter(Material.material_type == material_type)
    if file_source:
        query = query.filter(Material.file_source == file_source)
    
    # 添加模糊查询条件
    if search_keyword:
        query = query.filter(
            or_(
                Material.file_topic.like(f"%{search_keyword}%"),
                Material.company.like(f"%{search_keyword}%")
            )
        )
    
    # 按创建时间倒序排列
    query = query.order_by(desc(Material.create_time))
    
    # 计算总数
    total = query.count()
    
    # 分页
    materials = query.offset((page - 1) * page_size).limit(page_size).all()
    
    return {
        "materials": materials,
        "total": total,
        "page": page,
        "page_size": page_size,
        "total_pages": (total + page_size - 1) // page_size
    }
